from flask import Flask, render_template, request, redirect, url_for,jsonify # type: ignore
from flask_mysqldb import MySQL # type: ignore
from flask import session # type: ignore
import re  # 引入正则表达式模块

app = Flask(__name__)
app.secret_key = "liquor0505"  # 必须设置一个密钥来支持会话管理


# 配置 MySQL 数据库
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'liquor0505'
app.config['MYSQL_DB'] = 'sensor_db'

mysql = MySQL(app)

@app.route("/", methods=["GET", "POST"])  
def login():  
    if request.method == "POST":  
        username = request.form.get("username")  
        password = request.form.get("password")  
        
        if not username or not password:  
            return render_template("login.html", error="请填写用户名和密码！")  
        
        try:  
            cur = mysql.connection.cursor()  
            cur.execute("SELECT * FROM owners WHERE username = %s AND password = %s", (username, password))  
            user = cur.fetchone()  
            cur.close()  
             
            if user:  
                user_id, username, phone, password, root = user  # 获取用户信息，包括 root 字段
                session['user_id'] = user_id  # 保存用户 ID 到会话

                # 根据 root 值决定跳转页面
                if root == "0":
                    return redirect(url_for("main"))  # 普通用户跳转到普通用户主页面
                elif root == "1":
                    return redirect(url_for("admin_main"))  # 管理员跳转到管理员主页面
            else:  
                return render_template("login.html", error="用户名或密码错误！")  # Invalid login  

        except Exception as e:  
            return f"数据库查询出错: {e}", 500  # Error handling  

    return render_template("login.html")

@app.route("/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        username = request.form.get("username")
        phone = request.form.get("phone")
        password = request.form.get("password")
        model = request.form.get("model")
        manufacturer = request.form.get("manufacturer")
        
        if not (username and phone and password and model and manufacturer):
            return render_template("register.html", error="请填写所有字段！")
        
        try:
            cur = mysql.connection.cursor()
            
            # 插入车主信息
            cur.execute("INSERT INTO owners (username, phone,password) VALUES (%s, %s,%s)", (username, phone,password))
            user_id = cur.lastrowid  # 获取刚插入的用户ID
            
            # 插入车辆信息
            cur.execute("INSERT INTO vehicles (user_id, model, manufacturer) VALUES (%s, %s, %s)", 
                        (user_id, model, manufacturer))
            
            mysql.connection.commit()
            cur.close()
            
            return redirect(url_for("login"))  # 注册成功后跳转到登录页面
        except Exception as e:
            return f"注册失败: {e}", 500
    
    return render_template("register.html")


@app.route("/main")
def main():
    try:
        # 获取当前登录用户的 ID（假设通过会话保存 user_id）
        user_id = session.get('user_id')  # 确保在登录成功时设置 session['user_id']

        if not user_id:
            return redirect(url_for("login"))  # 如果未登录，重定向到登录页面

        # 查询用户和车辆信息
        cur = mysql.connection.cursor()
        query = """
            SELECT o.username, v.model, v.manufacturer
            FROM owners o
            INNER JOIN vehicles v ON o.user_id = v.user_id
            WHERE o.user_id = %s
        """
        cur.execute(query, (user_id,))
        vehicle_info = cur.fetchall()
        cur.close()

        # 渲染模板并传递数据
        return render_template("main.html", vehicle_info=vehicle_info)

    except Exception as e:
        return f"加载数据时发生错误: {e}", 500


@app.route("/action", methods=["GET", "POST"])
def action():
    if request.method == "POST":
        action_type = request.form.get("action_type")  # 获取 action_type 输入
        content = float(request.form.get("content"))  # 获取 action 内容并转换为 float 类型
        car_id = request.form.get("car_id")  # 获取车 ID（从表单或者会话获取）

        if not action_type or not content or not car_id:
            return render_template("action.html", error="请输入所有字段！")

        try:
            # 确保 content 是字符串类型
            content = str(content).strip()  # 确保 content 是字符串，并去除两端的空格

            # 使用正则表达式提取 content 中的数字部分
            match = re.match(r"(\d+(\.\d+)?)", content)  # 匹配数字或者带小数点的数字
            if not match:
                return render_template("action.html", error="内容输入无效，必须是数字！")

            # 提取数字部分并转换为 float 类型
            content = float(match.group(1))

            cur = mysql.connection.cursor()

             # 1. 根据 action_type 查找对应的 action_id 和多个 s_type
            cur.execute("SELECT a.action_id, a.a_type, i.s_type, i.content FROM action a "
                        "JOIN influence i ON a.action_id = i.action_id "
                        "WHERE a.a_type = %s", (action_type,))
            action_info = cur.fetchall()


            if not action_info:
                return render_template("action.html", error="未找到匹配的操作类型！")

           #遍历查询结果，构建 s_types 列表
            s_types = [(row[2], row[3]) for row in action_info]  # 获取所有 s_type

           # 2. 查找与 car_id 相关的传感器
            sensor_ids = []
            for s_type, _ in s_types:
                cur.execute("SELECT sensor_id FROM sensors WHERE car_id = %s AND s_type = %s", (car_id, s_type))
                sensors = cur.fetchall()
                for sensor in sensors:
                    sensor_ids.append(sensor[0])  # 获取传感器 ID

            if not sensor_ids:
                return render_template("action.html", error="未找到与此车辆和操作类型匹配的传感器！")

            # 3. 更新传感器数据
            for sensor_id, (s_type, i_content) in zip(sensor_ids, s_types):

                # 获取传感器的最新数据
                cur.execute("SELECT content FROM sensor_data WHERE sensor_id = %s ORDER BY time DESC LIMIT 1", (sensor_id,))
                last_content = cur.fetchone()

                # 如果没有数据，默认为 0
                if last_content:
                    last_content = float(last_content[0])
                else:
                    last_content = 0  # 如果没有数据，则默认设置为 0

                if content > last_content:
                    return render_template("action.html", error="输入的数据大于当前数据，操作失败！")

                if i_content == "加":
                    updated_content = last_content + content  # 加法
                elif i_content == "减":
                    updated_content = last_content - content  # 减法
                else:
                    updated_content = last_content  # 如果不支持的运算，保持原数据

                # 插入新的传感器数据记录
                cur.execute("INSERT INTO sensor_data (sensor_id, content, time) VALUES (%s, %s, NOW())",
                            (sensor_id, updated_content))

            # 提交事务
            mysql.connection.commit()
            cur.close()


            return render_template("action.html", result=f"您的车辆成功执行了'{action_type}'行为，数据：{content}")

        except Exception as e:
            return f"操作失败: {e}", 500  # 捕获异常

    return render_template("action.html")


@app.route("/query", methods=["GET","POST"])
def query():
    if request.method == "POST":
        sensor_id = request.form.get('sensor_id')
        
        if not sensor_id:
            return "未提供传感器编号", 400  # 返回 400 错误

        
        try:
             # 获取当前用户的 user_id
            user_id = session.get("user_id")
            if not user_id:
                return "用户未登录", 401  # 用户未登录
           
            # 查询传感器所属车辆和车主
            cur = mysql.connection.cursor()
            query = """
                SELECT sensors.sensor_id, sensors.s_type, sensors.location, sensors.status,vehicles.user_id
                FROM sensors
                JOIN vehicles ON sensors.car_id = vehicles.car_id
                WHERE sensors.sensor_id = %s
            """
            cur.execute(query, (sensor_id,),)

            result = cur.fetchone()

            
            # 检查查询结果
            if result:
                sensor_id, sensor_type, location, status, owner_id = result

                if owner_id == user_id:
                    # 查询最新的数据
                    cur.execute("""
                        SELECT content, time
                        FROM sensor_data
                        WHERE sensor_id = %s
                        ORDER BY time DESC LIMIT 1
                    """, (sensor_id,))
                    latest_data = cur.fetchone()

                    # 查询历史数据
                    cur.execute("""
                        SELECT content, time
                        FROM sensor_data
                        WHERE sensor_id = %s
                        ORDER BY time DESC
                    """, (sensor_id,))
                    history_data = cur.fetchall()

                    cur.close()

                    # 如果有最新数据
                    if latest_data:
                        latest_content, latest_time = latest_data
                    else:
                        latest_content, latest_time = "无数据", "无数据"

                    # 渲染模板，并传递数据
                    return render_template(
                        "query.html",
                        sensor_id=sensor_id,
                        sensor_type=sensor_type,
                        location=location,
                        status=status,
                        latest_content=latest_content,
                        latest_time=latest_time,
                        history_data=history_data
                    )
                else:
                    return "您无权查看该传感器的信息", 403  # 权限错误
            else:
                return "未找到对应的传感器信息", 404  # 返回 404 错误
        
        except Exception as e:
            return f"数据库查询出错", 500  # 返回 500 错误
    
    # GET 请求时渲染查询表单页面
    return render_template("query.html")


@app.route("/realtime")
def realtime():
    """展示实时数据的页面"""
    return render_template("realtime.html")

@app.route("/get_latest_sensor_data")
def get_latest_sensor_data():
    """从 realtime_data 表中获取最新的一条数据"""
    try:
        cur = mysql.connection.cursor()
        cur.execute("""
            SELECT id, time, temperature, weather, speed 
            FROM realtime_data 
            ORDER BY time ASC 
        """)
        all_data = cur.fetchall()
        cur.close()
        
        if not all_data:
            return jsonify({"error": "未找到数据"})
        
        # 使用 session 存储当前索引，初始为 0
        current_index = session.get("current_index", 0)
        
        # 获取当前索引处的数据
        record_id, time, temperature, weather, speed = all_data[current_index]
        
        # 计算下一个索引，循环到头部
        next_index = (current_index + 1) % len(all_data)
        session["current_index"] = next_index
        
        return jsonify({
            "id": record_id,
            "time": str(time),  # 将时间转换为字符串格式
            "temperature": f"{temperature}°C",
            "weather": weather,
            "speed": f"{speed} km/h"
        })
    
    except Exception as e:
        return jsonify({"error": f"数据库查询出错: {e}"})



@app.route("/logout")
def logout():
    session.clear()  # 清除会话数据
    return redirect(url_for("login"))


@app.route("/admin_main")
def admin_main():
    try:
        # 获取当前登录用户的 ID（假设通过会话保存 user_id）
        user_id = session.get('user_id')
        if not user_id:
            return redirect(url_for("login"))  # 如果未登录，重定向到登录页面

        # 获取管理员数据
        cur = mysql.connection.cursor()
        query = """
            SELECT o.username, v.model, v.manufacturer
            FROM owners o
            INNER JOIN vehicles v ON o.user_id = v.user_id
        """
        cur.execute(query)
        vehicle_info = cur.fetchall()
        cur.close()

        # 渲染管理员页面
        return render_template("admin_main.html", vehicle_info=vehicle_info)

    except Exception as e:
        return f"加载数据时发生错误: {e}", 500


@app.route("/search_by_manufacturer", methods=["POST"])
def search_by_manufacturer():
    manufacturer = request.form.get("manufacturer")
    
    if not manufacturer:
        return "请提供厂商名", 400  # 返回 400 错误
    
    try:
        # 查询车辆信息及车主信息
        cur = mysql.connection.cursor()
        query = """
            SELECT v.car_id, v.model, v.manufacturer, o.username, o.phone
            FROM vehicles v
            JOIN owners o ON v.user_id = o.user_id
            WHERE v.manufacturer = %s
        """
        cur.execute(query, (manufacturer,))
        vehicles = cur.fetchall()
        cur.close()
        
        if vehicles:
            # 如果查询到车辆信息，返回渲染页面
            return render_template("vehicle_info.html", vehicles=vehicles)
        else:
            return "未找到该厂商的车辆信息", 404  # 返回 404 错误
        
    except Exception as e:
        return f"查询出错: {e}", 500  # 返回 500 错误




@app.route("/search_by_car_and_date", methods=["POST", "GET"])
def search_by_car_and_date():
    # 获取表单中的车ID和日期
    car_id = request.form.get("car_id") if request.method == "POST" else request.args.get("car_id")
    date = request.form.get("date") if request.method == "POST" else request.args.get("date")
    
    if not car_id or not date:
        return "请提供车辆ID和日期", 400  # 返回 400 错误
    
    try:
        # 获取当前页和每页显示的条数
        page = int(request.args.get("page", 1))  # 默认为第一页
        per_page = 10  # 每页显示10条记录
        
        # 查询车辆拥有的所有传感器 ID
        cur = mysql.connection.cursor()
        query = """
            SELECT s.sensor_id
            FROM sensors s
            WHERE s.car_id = %s
        """
        cur.execute(query, (car_id,))
        sensor_ids = cur.fetchall()
        cur.close()

        if not sensor_ids:
            return "未找到该车的传感器信息", 404  # 返回 404 错误

        # 存储所有传感器的数据
        all_sensor_data = []

        # 遍历每个传感器，查询该传感器的数据
        for sensor in sensor_ids:
            sensor_id = sensor[0]

            # 查询该传感器在指定日期的数据
            cur = mysql.connection.cursor()
            query = """
                SELECT sd.sensor_id, sd.content, sd.time
                FROM sensor_data sd
                WHERE sd.sensor_id = %s AND DATE(sd.time) = %s
                ORDER BY sd.time
            """
            cur.execute(query, (sensor_id, date))
            sensor_data = cur.fetchall()
            cur.close()

            # 将查询结果添加到 all_sensor_data 列表
            if sensor_data:
                all_sensor_data.extend(sensor_data)  # 将每个传感器的数据合并到结果列表中

        # 如果没有找到任何数据，返回404
        if not all_sensor_data:
            return "未找到该日期的传感器数据", 404  # 返回 404 错误

        # 进行分页处理
        total_data = len(all_sensor_data)  # 数据总条数
        total_pages = (total_data + per_page - 1) // per_page  # 计算总页数
        offset = (page - 1) * per_page  # 计算偏移量

        # 截取当前页的数据
        page_data = all_sensor_data[offset:offset + per_page]

        return render_template("sensor_data.html", sensor_data=page_data, total_pages=total_pages, page=page, car_id=car_id, date=date)
    
    except Exception as e:
        return f"查询出错: {e}", 500  # 返回 500 错误





if __name__ == "__main__":
    app.run(debug=True)
